﻿/*
 * Created by SharpDevelop.
 * User: Dhanesh
 * Date: 12-10-2008
 * Time: 02:37:13
 * 
 * To change this template use Tools | Options | Coding | Edit Standard Headers.
 */

using System;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;

namespace ChallanSystem
{
	/// <summary>
	/// Description of Product.
	/// </summary>
	public class Product
	{
		private static string strCon = "Provider=Microsoft.Jet.OLEDB.4.0 ;Data Source=./Data/DB.mdb" ;
		private OleDbConnection myCon = new OleDbConnection(strCon);
		
		private string name;
		
		public string Name {
			get { return name; }
			set { name = value; }
		}
		
		private string price_retail;
		
		public string Price_Retail {
			get { return price_retail; }
			set { price_retail = value; }
		}
		
		private string price_semiretail;
		
		public string Price_SemiRetail {
			get { return price_semiretail; }
			set { price_semiretail = value; }
		}
		
		private string price_wholesale;
		
		public string Price_Wholesale {
			get { return price_wholesale; }
			set { price_wholesale = value; }
		}
		
		private string price_semiwholesale;
		
		public string Price_SemiWholesale {
			get { return price_semiwholesale; }
			set { price_semiwholesale = value; }
		}
		
		public Product()
		{
		}
		
		#region View()
		public DataSet View()
		{
			string sqlStr = "SELECT * FROM Products";
			//Make a DataSet object
			DataSet myDataSet = new DataSet();
			//Using the OleDbDataAdapter execute the query
			OleDbDataAdapter myAdapter = new OleDbDataAdapter( sqlStr , myCon );
			//Fill the DataSet with the Table 'Product'
			myAdapter.Fill(myDataSet,"Products");
			return myDataSet;
		}
		
		public DataSet View(int Customer_Type)
		{
			string temp = string.Empty;
			
			switch(Customer_Type)
			{
				case 1:
					temp = "Price_Retail";
					break;
				case 2:
					temp = "Price_SemiRetail";
					break;
				case 3:
					temp = "Price_Wholesale";
					break;
				case 4:
					temp = "Price_SemiWholesale";
					break;
			}
			
			string sqlStr = "SELECT Product_ID, Product_Name, "+ temp +" FROM Products";
			//Make a DataSet object
			DataSet myDataSet = new DataSet();
			//Using the OleDbDataAdapter execute the query
			OleDbDataAdapter myAdapter = new OleDbDataAdapter( sqlStr , myCon );
			//Fill the DataSet with the Table 'Product'
			myAdapter.Fill(myDataSet,"Products");
			return myDataSet;
		}
		
		public DataSet GetOrderHistory()
		{
			string sqlStr = "SELECT ORD.ORDER_ID, ORDPRD.PRODUCT_ID, ORDPRD.PRICE, PRD.PRODUCT_NAME FROM ORDERS AS ORD, CUSTOMERS AS CUST, ORDER_PRODUCTS AS ORDPRD, PRODUCTS AS PRD WHERE ORD.CUSTOMER_ID=CUST.CUSTOMER_ID AND ORD.CUSTOMER_ID=10 AND ORDPRD.ORDER_ID=ORD.ORDER_ID AND ORDPRD.PRODUCT_ID=PRD.PRODUCT_ID";
			//Make a DataSet object
			DataSet OrderHistoryDataSet = new DataSet();
			//Using the OleDbDataAdapter execute the query
			OleDbDataAdapter myAdapter = new OleDbDataAdapter( sqlStr , myCon );
			//Fill the DataSet with the Table 'Product'
			myAdapter.Fill(OrderHistoryDataSet);
			return OrderHistoryDataSet;
		}
		
		#endregion
		
		#region Update()
		public bool Update(int id)
		{
			bool done = false;
			
			if( name != "" && price_retail != "" && price_wholesale != null)
			{
				//the string to get values from the textboxes and form an "INSERT INTO"
				// statement.
				string sqlStr = "Update Products SET ";
				sqlStr += "Product_Name = '" + Convert.ToString(name) + "',";
				sqlStr += "Price_Retail = '" + Convert.ToString(price_retail) + "',";
				sqlStr += "Price_SemiRetail = '" + Convert.ToString(price_semiretail) + "',";
				sqlStr += "Price_Wholesale = '" + Convert.ToString(price_wholesale) + "',";
				sqlStr += "Price_SemiWholesale = '" + Convert.ToString(price_semiwholesale) + "' ";
				sqlStr += "WHERE  Product_ID = " + Convert.ToString(id);
				
				OleDbCommand myCmd = new OleDbCommand( sqlStr , myCon ) ;
				try
				{
					myCon.Open();
					//Execute the statement
					myCmd.ExecuteNonQuery();
					//statusBar.Text="Connected - Now you can add records";
					done = true;
					
				}
				catch( OleDbException a)
				{
					MessageBox.Show("Error in updating! \n" + a.ToString(), "Error");
				}
				finally
				{
					myCmd.Dispose();
					myCon.Close() ;
					//myCon.Dispose();
				}
			}
			else
			{
				MessageBox.Show("All mandatory fields must be filled.", "Error");
			}
			return done;
		}
		#endregion
		
		#region Add()
		public bool Add()
		{
			
			bool done = false;
			
			if( name != "" && price_retail != "" && price_wholesale != null)
			{
				//the string to get values from the textboxes and form an "INSERT INTO"
				// statement.
				string sqlStr = "INSERT INTO Products(Product_Name,Price_Retail,Price_SemiRetail,Price_Wholesale,Price_SemiWholesale) Values(";
				sqlStr += "'" + Convert.ToString(name) + "',";
				sqlStr += "'" + Convert.ToString(price_retail) + "',";
				sqlStr += "'" + Convert.ToString(price_semiretail) + "',";
				sqlStr += "'" + Convert.ToString(price_wholesale) + "',";
				sqlStr += "'" + Convert.ToString(price_semiwholesale) + "')";

				OleDbCommand myCmd = new OleDbCommand( sqlStr , myCon ) ;
				try
				{
					myCon.Open();
					//Execute the statement
					myCmd.ExecuteNonQuery();
					//statusBar.Text="Connected - Now you can add records";
					done = true;
					
				}
				catch( OleDbException a)
				{
					MessageBox.Show("Error in inserting! \n" + a.ToString(), "Error");
				}
				finally
				{
					myCmd.Dispose();
					myCon.Close() ;
					//myCon.Dispose();
				}
			}
			else
			{
				MessageBox.Show("All fields must be completed.", "Error");
			}
			return done;
		}
		#endregion
		
		#region Delete()
		public bool Delete(int id)
		{
			bool done = false;
					
			//the string to get values from the textboxes and form an "INSERT INTO"
			// statement.
			string sqlStr = "DELETE FROM Products ";
			sqlStr += "WHERE Product_ID = " + Convert.ToString(id);
			
			OleDbCommand myCmd = new OleDbCommand( sqlStr , myCon ) ;
			try
			{
				myCon.Open();
				//Execute the statement
				myCmd.ExecuteNonQuery();
				//statusBar.Text="Connected - Now you can add records";
				done = true;
				
			}
			catch( OleDbException a)
			{
				MessageBox.Show("Error in deleting! \n" + a.ToString(), "Error");
			}
			finally
			{
				myCmd.Dispose();
				myCon.Close() ;
				//myCon.Dispose();
			}
			
			return done;
		}
		#endregion
	}
}
